package com.wanda.util;


import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import com.wanda.model.DataModel;
import com.wanda.model.RoomPBUAPModel;

public class ExcelDataUtil {

	public static void main(String[] args) throws Exception {

		try {

			File file = new File("d://myroom.xls");
			String[][] result = getData(file, 1);
			int rowLength = result.length;
			for (int i = 0; i < rowLength; i++) {
				for (int j = 0; j < result[i].length; j++) {
					System.out.print(result[i][j] + "\t\t");
				}
				System.out.println();
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		}

	}

	/**
	 * 读取Excel的内容，第一维数组存储的是一行中格列的值，二维数组存储的是多少个行
	 * 
	 * @param file
	 *            读取数据的源Excel
	 * @param ignoreRows
	 *            读取数据忽略的行数，比喻行头不需要读入 忽略的行数为1
	 * @return 读出的Excel中数据的内容
	 * @throws FileNotFoundException
	 * @throws IOException
	 */
	public static String[][] getData(File file, int ignoreRows)
			throws FileNotFoundException, IOException {
		List<String[]> result = new ArrayList<String[]>();
		int rowSize = 0;
		BufferedInputStream in = new BufferedInputStream(new FileInputStream(
				file));
		// 打开HSSFWorkbook
		POIFSFileSystem fs = new POIFSFileSystem(in);
		HSSFWorkbook wb = new HSSFWorkbook(fs);
		HSSFCell cell = null;
		for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
			HSSFSheet st = wb.getSheetAt(sheetIndex);
			// 第一行为标题，不取
			for (int rowIndex = ignoreRows; rowIndex <= st.getLastRowNum(); rowIndex++) {
				HSSFRow row = st.getRow(rowIndex);
				if (row == null) {
					continue;
				}
				int tempRowSize = row.getLastCellNum() + 1;
				if (tempRowSize > rowSize) {
					rowSize = tempRowSize;
				}
				String[] values = new String[rowSize];
				Arrays.fill(values, "");
				boolean hasValue = false;
				for (short columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) {
					String value = "";
					cell = row.getCell(columnIndex);
					if (cell != null) {
						// 注意：一定要设成这个，否则可能会出现乱码
						// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
						switch (cell.getCellType()) {
						case HSSFCell.CELL_TYPE_STRING:
							value = cell.getStringCellValue();
							break;
						case HSSFCell.CELL_TYPE_NUMERIC:
							if (HSSFDateUtil.isCellDateFormatted(cell)) {
								Date date = cell.getDateCellValue();
								if (date != null) {
									value = new SimpleDateFormat("yyyy-MM-dd")
											.format(date);
								} else {
									value = "";
								}
							} else {
								value = new DecimalFormat("0").format(cell
										.getNumericCellValue());
							}
							break;
						case HSSFCell.CELL_TYPE_FORMULA:
							// 导入时如果为公式生成的数据则无值
							if (!cell.getStringCellValue().equals("")) {
								value = cell.getStringCellValue();
							} else {
								value = cell.getNumericCellValue() + "";
							}
							break;
						case HSSFCell.CELL_TYPE_BLANK:
							break;
						case HSSFCell.CELL_TYPE_ERROR:
							value = "";
							break;
						case HSSFCell.CELL_TYPE_BOOLEAN:
							value = (cell.getBooleanCellValue() == true ? "Y"
									: "N");
							break;
						default:
							value = "";
						}
					}
					if (columnIndex == 0 && value.trim().equals("")) {
						break;
					}
					values[columnIndex] = rightTrim(value);
					hasValue = true;
				}

				if (hasValue) {
					result.add(values);
				}
			}
		}
		in.close();
		String[][] returnArray = new String[result.size()][rowSize];
		for (int i = 0; i < returnArray.length; i++) {
			returnArray[i] = (String[]) result.get(i);
		}
		return returnArray;
	}

	/**
	 * 去掉字符串右边的空格
	 * 
	 * @param str
	 *            要处理的字符串
	 * @return 处理后的字符串
	 */
	public static String rightTrim(String str) {
		if (str == null) {
			return "";
		}
		int length = str.length();
		for (int i = length - 1; i >= 0; i--) {
			if (str.charAt(i) != 0x20) {
				break;
			}
			length--;
		}
		return str.substring(0, length);
	}
    public static void exportExcel_Code(List<String> header,
            List<DataModel>Myroomlist, String sheetName,String path) {
            OutputStream output = null;
            try {
                output = new FileOutputStream(path);
            } catch (Exception ex) {
                ex.printStackTrace();
            }

    HSSFWorkbook wb = new HSSFWorkbook();

    HSSFSheet sheet1 = wb.createSheet("所选房间信息列表");// 创建工作表

    HSSFCellStyle cellstyle = wb.createCellStyle();// 单元格样式

    /*--------------------设置列头得样式表-------------*/
    HSSFFont font = wb.createFont();

    font.setBoldweight((short) 20);

    // font.setFontName("宋体");

    font.setFontHeightInPoints((short) 10);

    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

    /*---------------------------------------------------*/

    cellstyle.setFont(font);

    HSSFRow row = sheet1.createRow(0);// 创建工作表中的一行---------每一列的列头

    for (int i = 0; i < header.size(); i++) {

            HSSFCell cell1 = row.createCell(i);

            cell1.setCellStyle(cellstyle);// 设置单元格样式

            cell1.setCellValue(header.get(i));

    }

    if (Myroomlist.size() > 0) {

            for (int j = 0; j < Myroomlist.size(); j++) {

            	DataModel data = (DataModel)Myroomlist.get(j);

				row.createCell(0).setCellValue(data.getiD());
				row.createCell(1).setCellValue(data.getTask_ID());
				row.createCell(2).setCellValue(data.getData_URL());
				row.createCell(3).setCellValue(data.getField_1());
				row.createCell(4).setCellValue(data.getField_2());
				row.createCell(5).setCellValue(data.getField_3());
				row.createCell(6).setCellValue(data.getField_4());
				row.createCell(7).setCellValue(data.getField_5());
				row.createCell(8).setCellValue(data.getField_6());
				row.createCell(9).setCellValue(data.getField_7());
				row.createCell(10).setCellValue(data.getField_8());
				row.createCell(11).setCellValue(data.getField_9());
				row.createCell(12).setCellValue(data.getField_10());
				row.createCell(13).setCellValue(data.getField_11());
				row.createCell(14).setCellValue(data.getField_12());
				row.createCell(15).setCellValue(data.getField_13());
				row.createCell(16).setCellValue(data.getField_14());
				row.createCell(17).setCellValue(data.getField_15());
				row.createCell(18).setCellValue(data.getField_16());
				row.createCell(19).setCellValue(data.getField_17());
				row.createCell(20).setCellValue(data.getField_18());
				row.createCell(21).setCellValue(data.getField_19());
				row.createCell(22).setCellValue(data.getField_20());
				row.createCell(23).setCellValue(data.getField_21());
				row.createCell(24).setCellValue(data.getField_22());
				row.createCell(25).setCellValue(data.getField_23());
				row.createCell(26).setCellValue(data.getField_24());
				row.createCell(27).setCellValue(data.getField_25());
				row.createCell(28).setCellValue(data.getField_26());
				row.createCell(29).setCellValue(data.getField_27());
				row.createCell(30).setCellValue(data.getField_28());
				row.createCell(31).setCellValue(data.getField_29());
				row.createCell(32).setCellValue(data.getField_30());
				row.createCell(33).setCellValue(data.getField_31());
				row.createCell(34).setCellValue(data.getField_32());
				row.createCell(35).setCellValue(data.getField_33());
				row.createCell(36).setCellValue(data.getField_34());
				row.createCell(37).setCellValue(data.getField_35());
				row.createCell(38).setCellValue(data.getField_36());
				row.createCell(39).setCellValue(data.getField_37());
				row.createCell(40).setCellValue(data.getField_38());
				row.createCell(41).setCellValue(data.getField_39());
				row.createCell(42).setCellValue(data.getField_40());
				row.createCell(43).setCellValue(data.getField_41());
				row.createCell(44).setCellValue(data.getField_42());
				row.createCell(45).setCellValue(data.getField_43());
				row.createCell(46).setCellValue(data.getField_44());
				row.createCell(47).setCellValue(data.getField_45());
				row.createCell(48).setCellValue(data.getField_46());
				row.createCell(49).setCellValue(data.getField_47());
				row.createCell(50).setCellValue(data.getField_48());
				row.createCell(51).setCellValue(data.getField_49());
				row.createCell(52).setCellValue(data.getField_50());
				row.createCell(53).setCellValue(data.getCreate_User());
				row.createCell(54).setCellValue(data.getCreate_Time());
				row.createCell(55).setCellValue(data.getUpdate_User());
				row.createCell(56).setCellValue(data.getUpdate_time());                
            }

    }

    try {

            output.flush();

            wb.write(output);

            output.close();

    } catch (IOException e) {

            e.printStackTrace();

            System.out.println("Output   is   closed ");

    }

}
}

